package com.huayi.website.repository;

import com.huayi.website.entity.BannerEntity;
import com.huayi.website.entity.EnumPages;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class BannerRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 获取所有分类
     * @return
     */
    public List<BannerEntity> getAllList(EnumPages webColumn) {
        String sql = "SELECT * FROM "+Tables.Banner+ "WHERE 1=1 ";
        List<Object> params = new ArrayList<>();
        if(webColumn!=null){
            sql+=" AND column_id=?";
            params.add(webColumn.getIndex());
        }
        sql+=" ORDER BY sort desc, id desc";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(BannerEntity.class),params.toArray(new Object[params.size()]));
    }

    public BannerEntity getById(Integer bannerId){
        try{
            return jdbcTemplate.queryForObject("SELECT * FROM "+Tables.Banner+" WHERE id=?",new BeanPropertyRowMapper<>(BannerEntity.class),bannerId);
        }catch (Exception e){
            return null;
        }
    }

    public void deleteBanner(Integer bannerId){
        jdbcTemplate.update("DELETE FROM "+Tables.Banner +" WHERE id=?",bannerId);
    }

    public Integer editBanner(Integer bannerId, String title,String url, Integer sort, String image,Integer columnId) {
        if(bannerId==null || bannerId<=0){
            //添加
            String sql = "INSERT INTO " + Tables.Banner + "(title,url,img_url,sort,column_id) VALUE (?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, title);
                    ps.setString(2, url);
                    ps.setString(3, image);
                    ps.setInt(4, sort);
                    ps.setInt(5, columnId);

                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            return id;
        }else{
            //修改
            jdbcTemplate.update("UPDATE "+Tables.Banner+" SET title=?,url=?,img_url=?,sort=?,column_id=? WHERE id=?",title,url,image,sort,columnId,bannerId);
            return bannerId;
        }
    }
}
